package com.bsh.repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import com.bsh.mybatis.IRepository.PartnerMapper;
import com.bsh.mybatis.dataView.PartnerExt;
import com.bsh.mybatis.model.Partner;
import com.bsh.utils.JdbcHelper;
import com.bsh.utils.Pager;
import com.mysql.jdbc.StringUtils;

@Repository
public class DaoPartner {
	@Resource
	SqlSessionTemplate session;
	PartnerMapper getPartnerMapper(){
		return session.getMapper(PartnerMapper.class);
	}
	@Resource
	JdbcTemplate jdbcTemplate;
	@Resource
	NamedParameterJdbcTemplate namedJdbcTemplate;
	@Resource
	JdbcHelper jh;

	PartnerMapper getMapper() {
		return session.getMapper(PartnerMapper.class);
	}

	public int deleteByPrimaryKey(Integer id) {
		int res = getMapper().deleteByPrimaryKey(id);
		return res;
	}

	public int insert(Partner record) {
		int res = getMapper().insert(record);
		return record.getId();
	}

	public int insertSelective(Partner record) {
		int res = getMapper().insertSelective(record);
		return record.getId();
	}

	public Partner selectByPrimaryKey(Integer id) {
		Partner row = getMapper().selectByPrimaryKey(id);
		return row;
	}

	public int updateByPrimaryKeySelective(Partner record) {
		int res = getMapper().updateByPrimaryKeySelective(record);
		return res;
	}

	public int updateByPrimaryKey(Partner record) {
		int res = getMapper().updateByPrimaryKey(record);
		return res;
	}

	/**
	 * 成为合作伙伴分页查询
	 */
	public List<Map<String, Object>> getPage(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("SELECT v.*,pv.name level_name, u. name user_name,u.email,u.mobile,u.head_img,");
		sb.append("rp.name province_name,rc.name city_name,ra.name area_name ");
		sb.append("FROM partner v ");
		sb.append("left JOIN region rp ON rp.id = v.province ");
		sb.append("left JOIN region rc ON rc.id = v.city ");
		sb.append("left JOIN region ra ON ra.id = v.area ");
		sb.append("join user u on u.id=v.userid ");
		sb.append("join partner_level pv on pv.id=v.partner_level ");
		sb.append("where status=1 ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by v.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}

	/**
	 * 成为合作伙伴申请分页查询
	 */
	public List<Map<String, Object>> getAskPage(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("SELECT v.*,pv.name level_name, u. name user_name,u.email,u.mobile,");
		sb.append("rp.name province_name,rc.name city_name,ra.name area_name ");
		sb.append("FROM partner v ");
		sb.append("left JOIN region rp ON rp.id = v.province ");
		sb.append("left JOIN region rc ON rc.id = v.city ");
		sb.append("left JOIN region ra ON ra.id = v.area ");
		sb.append("join user u on u.id=v.userid ");
		sb.append("join partner_level pv on pv.id=v.partner_level ");
		sb.append("where (status=0 or status=2) ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by v.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}

	/**
	 * 添加合伙人
	 * @param userid
	 * @return
	 */
	public Integer addPartner(Partner partner){
		PartnerMapper map = this.getMapper();
		return map.addPartner(partner);
	}

	
	/**
	 * 审核
	 * @param userid
	 * @return
	 */
	public Integer audit(int partnerId, boolean isPass, String auditMsg) {
		PartnerMapper map = this.getMapper();
		if (isPass)
			return map.auditPass(partnerId, new Date());
		else
			return map.auditRefuse(partnerId, auditMsg, new Date());
	}
	
	/**
	 * 修改合伙人
	 * @param merchant
	 * @return
	 */
	public Integer editPartner(Partner partner) {
		PartnerMapper map = this.getMapper();
		int mt = map.editPartner(partner);
		return mt;
	}
	
	/**
	 * 查询合作伙伴信息
	 * @param id
	 * @return
	 */
	public PartnerExt getPartnerInfo(Integer id) {
		PartnerExt row = getMapper().getPartnerInfo(id);
		return row;
	}
	
	/**
	 * 查询对应县区合伙伴
	 * RecommendServiceImpl 推荐返币使用！ 谨慎修改
	 * 注意加条件 status = 1 是审核通过
	 * @param province
	 * @param city
	 * @param area
	 * @return
	 */
	public List<Map<String, Object>> getAreaPartner(Integer province,Integer city,Integer area) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("select p.* from partner p ");
		sb.append("join user u on u.id=p.userid ");
		//注意加条件 status = 1 是审核通过 jane
		sb.append("where p.partner_level=4 and p.status=1 ");
		sb.append(String.format(" and p.province = %d and p.city = %d and p.area = %d  ", province, city, area ));
	
		sb.append(" order by p.id asc ");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		return list;
	}
	
	/**
	 * 查询对应市合伙伴
	 * RecommendServiceImpl 推荐返币使用！ 谨慎修改
	 * 注意加条件 status = 1 是审核通过
	 * @param province
	 * @param city
	 * @return
	 */
	public List<Map<String, Object>> getCityPartner(Integer province,Integer city) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("select p.* from partner p ");
		sb.append("join user u on u.id=p.userid ");
		//注意加条件 status = 1 是审核通过 jane
		sb.append("where p.partner_level=3 and p.status=1 ");
		sb.append(String.format(" and p.province = %d and p.city = %d ", province, city ));
	
		sb.append(" order by p.id asc ");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		return list;
	}
	
	/**
	 * 查询对应省级合伙伴
	 * RecommendServiceImpl 推荐返币使用！ 谨慎修改
	 * 注意加条件 status = 1 是审核通过
	 * @param province
	 * @return
	 */
	public List<Map<String, Object>> getProvincePartner(Integer province) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("select p.* from partner p ");
		sb.append("join user u on u.id=p.userid ");
		//注意加条件 status = 1 是审核通过 jane
		sb.append("where p.partner_level=2 and p.status=1 ");
		sb.append(String.format(" and p.province = %d ", province ));
	
		sb.append(" order by p.id asc ");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		return list;
	}
	
	/**
	 * 查询全国合伙伴
	 * RecommendServiceImpl 推荐返币使用！ 谨慎修改
	 * 注意加条件 status = 1 是审核通过
	 * @return
	 */
	public List<Map<String, Object>> getCountryPartner() {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("select p.* from partner p ");
		sb.append("join user u on u.id=p.userid ");
		//注意加条件 status = 1 是审核通过 jane
		sb.append("where p.partner_level=1 and p.status=1 ");
		
		sb.append(" order by p.id asc ");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		return list;
	}
	
	/**
	 * 通过userid查询合伙人申请信息
	 * @param userid
	 * @return
	 */
	public List<Map<String, Object>> getPartnerByUserid(Integer userId) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append(" SELECT m.*,pl.name AS levelName,");
		sb.append(" rp.name AS provinceName,rc.name as cityName, ra.name AS areaName FROM partner m ");
 		sb.append(" JOIN user u on u.id=m.userid ");
		sb.append(" LEFT JOIN partner_level pl on pl.id=m.partner_level ");
		sb.append(" LEFT JOIN region rp on rp.id=m.province ");
		sb.append(" LEFT JOIN region rc on rc.id=m.city ");
		sb.append(" LEFT JOIN region ra on ra.id=m.area ");
		//商家状态为1 
		sb.append(" where status<>99 AND");
		sb.append(String.format("  m.userid = %d ", userId ));

		sb.append(" order by m.create_time desc  ");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		return list;
	}
	
	
	/**
	 * 查询合伙人信息
	 * @param userid
	 * @return
	 */
	public List<Map<String, Object>> getUpgradePartnerByUserid(Integer userId,Integer status) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append(" SELECT m.*");
		sb.append(" FROM partner m ");
 		sb.append(" JOIN user u on u.id=m.userid ");
		sb.append(" WHERE 1=1 AND m.status<>99 ");
		if(status>-1){
			sb.append(String.format("AND  m.status = %d ", status ));
		}
		sb.append(String.format("AND  m.userid = %d ", userId ));
		sb.append(" ORDER BY m.create_time desc  ");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		return list;
	}
	
	/**
	 * 检测用户在指定区域是否已有相同级别
	 * @return
	 */
	public Partner checkInAreaLevel(int userid,int country,int province,int city,int area,int partnerLevel){
		return this.getMapper().checkInAreaLevel(userid, country,province, city, area, partnerLevel);
	}
	
	/**
	 * 升级合伙人
	 * @return
	 */
	public Integer upgrade(int partnerId,int userid,int upLevel){
		return this.getMapper().upgrade(partnerId,userid,upLevel);
	}	

	/**
	 * 作废
	 * @param partnerId
	 * @return
	 */
	public Integer disable(int partnerId){
		String sql="update partner set status=99 where id = :id";
		Map<String,Object> ps=new HashMap<String,Object>();
		ps.put("id", partnerId);
		return jh.execute(sql, ps);
	}
	

	/**
	 * 失效合作伙伴分页查询
	 */
	public List<Map<String, Object>> getRemovedPage(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("SELECT v.*,pv.name level_name, u. name user_name,u.email,u.mobile,u.head_img,");
		sb.append("rp.name province_name,rc.name city_name,ra.name area_name ");
		sb.append("FROM partner v ");
		sb.append("left JOIN region rp ON rp.id = v.province ");
		sb.append("left JOIN region rc ON rc.id = v.city ");
		sb.append("left JOIN region ra ON ra.id = v.area ");
		sb.append("join user u on u.id=v.userid ");
		sb.append("join partner_level pv on pv.id=v.partner_level ");
		sb.append("where v.status=99 ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by v.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}
}
